Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to learn some cool conditional formatting tricks.
So you got your coffee mug? well, lets start!
The 5 tricks we are going to learn are,
1. Highlighting alternative rows / columns in tables
2. No-nonsense project plans / gantt charts
3. Extreme Incell graphs
4. Highlight mistakes, errors, omissions, repetitions
5. Create intuitive dashboards
If you are new to Excel Conditional Formatting, please read the Conditional Formatting Basics article before proceeding.
I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star
1. Highlighting alternative rows / columns in tables:
Often when you present data in a large table it looks monotonous and is difficult to read. This is because your eyes start interpreting the data as grid instead of some important numbers. To break this you try highlighting or changing the background color of alternative rows / columns. But how would you do this if you have rather large table and it keeps changing. The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).
- First select data part of the table you want to format.
- Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
- Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
- Now, if you want to highlight alternative rows, the formula can go something like this,
=MOD(ROW(),2)=0
which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.
- Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
- Click OK.
- Congratulations, you have mastered a conditional formatting trick now 🙂
2. Creating a quick project plan / gantt chart using conditional formatting:
Project plans / gantt charts are everyday activity in most of our lives. Creating a simple and snazzy project plan template in excel is not a difficult job, using conditional formatting a bit of formulas you can do it no time.
- First create a table structure like shown above, with columns like Activity, start and end day, day 1, 2,3, etc…
- Now, whenever a day falls between start and end day for a corresponding activity, we need to highlight that row. For that we need to identify whether a day falls between start and end. We can do that with the below formulas,
=IF(AND(F$8>=$D9, F$8<=$E9),"1","")
Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell.
- Next, whenever the cell value is 1, we will just fill the cell with a favorite color and change the font to same color, so that we dont see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. This will be done by conditional formatting like below:
- Congratulations, you have mastered the art of creating excel gantt charts now
3. Extreme In-cell Graphs:
Incell graphing is a nifty trick that basically uses REPT() function (used to repeat a string, character given number of times) to generate bar-charts with in a cell. You can apply conditional formatting on top of them to give the charts a good effect. Here is a sample:
The above is a table of visits to Chandoo.org 😉 in the month of January 2008. As you can see I have highlighted (by changing the font color to red and making it bold) for the cells that have more than average number of visits in the month. I am not going to tell you how to do it, it is your home work 🙂
4. Highlight mistakes / errors / omissions / repetitions using conditional formatting:
Often we will do highly monotonous job like typing data in a sheet. Since the work is monotonous you tend to make mistakes, omit a few or repeat something etc. This can be avoided by conditional formatting. I use this trick whenever I am typing something or pasting a formula over a rather large range of cells (for eg. vlookup on annual revenue data of all your accounts, could run in to thousands of rows across multiple states /regions etc.).
Lets see how you can highlight a cell when it has an error:
- First select the cells that you want to search for errors
- Next go to menu > format > conditional formatting and mention the formula as:
=iserror()(see below)
- In the same way you track repetitions, a simple
countif()would do the magic for you, or Omissions (again a countif()) - Thats it, you have learned how to save tons of time by letting excel do the job for you. Sit back and sip that coffee before it gets cold.
5. Creating dash boards using excel conditional formatting:
As I said before you can use conditional formatting to create intuitive sales reports or analytics outputs. Like the one shown here,
Here is how you can do it:
- Copy your data table to a new table.
- Empty the data part and replace it with formula that can go like this (I am using the above table format to write these formulas, may change for your data)
=ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.
- Next, conditionally change the color of cell to red / green / blue or pink (if you want 😉 ) and you are done
- Show it to your boss, bask in the glory 🙂
I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star.
More DIY Excel Training:
» Excel for Beginners – Tutorials
» Excel Formula Examples
» Pivot Tables
» Project Management using Excel
» Excel Dashboards
» Advanced Excel
345 Responses to “Conditional Formatting in Excel – 5 Tips to make you a Rockstar”
[...] Gantt chart template here. Here’s more on using conditional formatting in your spreadsheets. Learn Cool Microsoft Excel Conditional Formatting Tricks [...]
[...] [view original post] [source: Delicious] Previously - Google Sky Next - [...]
[...] [view original post] [source: Delicious] Previously - Google Sky Next - [...]
[...] Gantt chart template here. Here’s more on using conditional formatting in your spreadsheets. Learn Cool Microsoft Excel Conditional Formatting Tricks [...]
Hi Chandoo, got here via Lifehacker and just wanted to say what a lifesaver this is! I don't know who in the world picked the default colors for Excel charts, but they are truly hideous. (You'd think Microsoft can afford to hire someone with better sense aesthetics.) Thanks for bringing in some beauty into the corporate world indeed.
This is one of the web's most interesting stories on Fri 14th Mar 2008
These are the web's most talked about URLs on Fri 14th Mar 2008. The current winner is ..
[...] Read the rest of this great post here [...]
[...] information would be helpful. So what follows is for them or you, if you flivewithdesire.typepad.comWant to be an Excel Conditional Formatting Rock Star ? Read this Excel conditional formatting is a hidden and powerful gem that when used well, can change the [...]
[...] the rest of the post here Share and Enjoy: These icons link to social bookmarking sites where readers can share and [...]
Hi chandoo...nice tips and i would use it in my work. So where are you now? based out chennai or moved to
US for a long term. I think you are now still with TCS. rite?
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save tim... (tags: awesome cool guide how-to office technique tips formatting Excel) No Comments so far Leave a comment RSS feed for comments on this post. TrackBack URI Leave a comment Line and paragraph breaks automatic, e-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> [...]
Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save time, impress everyone) | Pointy Haired Dilbert - Chandoo.org
[...][...]
Thanks Chandoo!! I've never seen these features and will use them daily :O)
In the Gantt chart example, instead of matching the font with the cell color, you can also simply apply a custom formatting of ";;;" which hides any text in the cell. That way you can feel free to mix and match colors (or even apply some conditional formatting on colors of the Gantt chart) without worrying about changing the font, too.
For the Gantt Chart:
You could ignore the contents of the cell altogether if you put =AND(F$8>=$D9, F$8<=$E9) directly into the conditional formatting "Formula Is" section and filled that out into the rest of the cells.
Neater, no?
5 Cool Excel Conditional Formatting Tricks
Excel conditional formatting is a hidden and powerful gem that when used well can change the outlook of your project report sales budget project plan or
[...] I am overwhelmed by the response the Excel Conditional Formatting Rockstar post generated. I got both lifehacked and del.icio.us home paged on the sameday and Wow, I am so happy [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save tim... (tags: excel howto formatting tips productivity software conditional) [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save tim... How MS Excell can be fun (tags: excel howto formatting productivity tips conditional) [...]
[...] Microsoft Excel Conditional Formatting Tricks (tags: microsoft excel conditional formatting software office) [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks (tags: software) [...]
[...] Gantt chart template here. Here’s more on using conditional formatting in your spreadsheets. Learn Cool Microsoft Excel Conditional Formatting Tricks [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save tim... (tags: excel howto business) [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks - Be a Rock star (Tips | How tos - Save tim... (tags: excel software) [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks 如 alternate row color, gantt chart [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks 如 alternate row color, gantt chart [...]
Okay I need help here, O Excel Master.
Let's say I have a task that needs to be done by a colleague in 3 hours. How do I make a cell change colour when that specific time is reached? I tried many ways using conditional formatting but the cell just won't change colour when the time comes!
Wow, I am overwhelmed by the comments and pingbacks, thanks everyone.
@talljoanne: thanks, welcome to PHD 🙂
@Krishna: welcome back, I with tcs, moved to us though
@Berry Zito: thanks, welcome to PHD 🙂
@RKB: that is a cool trick, I shall use it going forward, thanks for sharing your tips with PHD readers.
@Wayne Elgin: Pretty neat trick indeed, reduces the need to paste the formula over a large range, thanks for sharing it, welcome to PHD 🙂
@magenie: Thanks for dropping by. Hmm, that could be little tricky, but I am sure we can do this. I remember doing this for dates onces (when a deadline reaches that column would highlight), lets see how we can do this :
1. let me assume that we have task desc in cell a1
2. task target time (3:30 PM for eg.) in cell a2
3. We aim to change the color of cell a1 and a2 to red (or whatever) when the time is 3:30
4. The conditional formula could be something like :
=$a$2=now()
5. Now() would tell me what the date and time now is. It may not work correctly since now() would return date also, you can avoid this by converting date-time to only time using hour(), minutes() functions I guess.
6. Once you are done you can press "f9" every now and then. This will force excel to recalculate formulas or if you close your workbook and reopen excel would do the same ideally (unless you explicitly changed the options)
let me know if this helps you, welcome to PHD 🙂
Thanks for the speedy response, Chandoo!
I've actually tried that method earlier, but couldn't set the NOW() function in the conditional formatting field to only calculate the time. This are some of the other things I attempted (without success):
1) Fill in two cells, one (A1) with NOW(), formatted to display current time only, and another cell (A2) with target time. Then set conditional formatting so that A1 changes color when cell value =A2.
2) Fill a cell (B1) with target time including date so that I could use set conditional formatting for B1 to change color when cell value equals to =NOW()=B1.
I can't remember what other methods I've tried despite knowing it wasn't gonna work. Sigh..
I've used Excel 2003 and 2007 as well. Both failed me. Or maybe I failed them..
Yelp...
@magenie : hmm
first let me respond to your comment -
1) fill in two cells, one (A1)....
the value a cell displays is different from the underlying value of the cell, display value is based on the format you select, but when you access the cell through formulas, excel will pick up the background value which could be diff from display value, may be this is why you are having trouble doing the conditional check.
also, excel now() function returns time in seconds (and probably in milli seconds as well, not sure) so your conditional formatting may turn off even before you notice it.
Now, let me give you another solution...
in A1- enter time in "10:00 AM" format, just type in the value, excel will know that you have entered time and format the cell accordingly.
in A2 - enter the following formula "=TIME(HOUR(NOW()),MINUTE(NOW()),0)", essentially I am extracting current hour and minute and manually setting seconds to zero to create a time.
now change color of A1/A2 when A1=A2 (make sure you put the correct cell address i.e. $a$1=$a$2 if needed)
I have tried this in a sheet and it seems to be working fine. Let me know if this works for you. I can send you the excel if you want.
[...] formatação condicional no Excel (ou qualquer outra folha de cálculo). O artigo em causa, “Want to be an Excel Conditional Formatting Rock Star? Read This“, enumera e explica 5 técnicas que poderá utilizar na formação das suas tabelas mais [...]
It works, it works! 🙂
At first it didn't coz I used "Cell value".. I'm always unsure when to use cell value and when to use formula, since I entered the target time (a value) into the first cell, and not a formula.
But no matter, as long as it works. You ze best, Chandoo. 🙂
Great ideas and many thanks.
Can u please give examples for conditional formatting in open office Calc instead of using Excel?
@magenie .. I am happy it is working for you 🙂
@TCL : welcome to PHD, thanks for your comments.
Hmm, Open office calc is not yet installed on my laptop, may be this weekend I will spend sometime with it and start writing about it as well.
How do you insert the up and down arrows in the formulas on the dashboard (item 5)?
@Mike .. thanks for visiting PHD, welcome...
you can insert up /down arrows from menu > insert > symbols (then you have to look up the symbol you want), the list will have all the symbols your current font has (you can even change font form that dialog)
Let me know if this is not working for you..
This is great stuff! I was trying to find out how to fix a problem I was having with a conditional formatting. I have a worksheet where I enter 1 or 0. For 1, I want to highlight the cell green (that part works fine) for 0, I highlight red. The problem is that Excel thinks all blank cells are 0's and turns the whole thing red! Do you have any suggestions?
Also, do you use pivot tables at all? Thanks!
@Cheryl - Thanks for visiting PHD and commenting 🙂
coming to your question : "...The problem is that Excel thinks all blank cells are 0’s and turns the whole thing red!..."
you can try the conditional formatting with functions (instead of cell values) and give a formula that goes like,
=and(isnumber(cell),cell=0)
that is if you must use conditional formatting on ZERO, easier option is to replace zeros with some other value and excel would not mistake them for empty cells. Another option would be to replace empty cells in your table with some default value (like a hyphen - or something)
Yeah, I do use pivot tables, may be sometime I will write about those here 🙂
Thanks Chandoo. Of course it would have been easier to change the zero to a different value... but my boss was insistent on 1's and zero's.
I look forward to reading about pivot tables.. I use them all the time. I tried to get your charts to work in a pivot chart but didn't get it to work. : (
[...] Also: Download 73 beautiful excel chart templates | Art of excel charting | Become a conditional formatting pro [...]
[...] Learn Cool Microsoft Excel Conditional Formatting Tricks [...]
hi
how do you convert numbers to figures eg 1207 to one thousand two hundred & seven
@ Manish .. thanks for the comments, welcome to PHD...
try this UDF http://www.ozgrid.com/VBA/ValueToWords.htm to convert a value to words, not sure if that works for all the values... let me know if this helps 🙂
[...] read: Learn how to create project plans / gantt charts using conditional formatting Create art grade excel charts with these 73 designer templates Other uber cool excel tricks to make [...]
Chandoo, I love this page!
I have a question, I don't think it's achievable though.
Let's say I have a 'pass value' of 10. Anything above 10 will be green (it's above the pass value) and anything below 10 will be red.
This is easy to do with conditional formatting as per your example.
What I'd like to do is that in a cell where the value is 12 (hence green), and where its predecessor was higher, then I'd like to have the down arrow next to the green 12 to be red.
So the 12 will be green Still above the 'pass value' but the arrow will be red (since the value is lower than its predecessor).
Let me know what you think, I looked around but could not get that to work.
Thanks again 🙂
[...] All you need to do is add an additional column at the end of the table and fill it with 1s and 0s. (just enter 1 and 0 in 2 rows, select both of them and drag till the end of the table). Now we can use this column to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table] [...]
Hey Chandoo,
Stumbled here while reading one of your more recent entries.
Here's my excel headache of the day - There seems to be a limit of 3 conditional formatting rules you can set up on a set of data. You can make it 4, if you take not having any rules set up as one of the rules.
What if I need to highlight 5 sets of data with 5 different colours? Is it possible through conditional formatting?
[...] Be an Excel Conditional Formatting Rock star I have created an excel sheet containing all these examples. Feel free to download the excel and be a conditional formatting rock star (tags: excel tips) Possibly related posts: (automatically generated)links for 2008-05-27links for 2008-05-22Flexible Rails Book - Part OneScaling to Enterprise [...]
@Gaetan .. thanks for visiting PHD, I couldnt respond any earlier. Somehow I thought I had answered your query, my mistake. Well, As far as I know, its not possible to color different parts of a cell with different formats using conditional formatting or formulas. You can do this with VBA though.
A simple workaround could be to have another column next to the one with values solely to display arrows and use conditional formatting on it based on the values in the data column. I hope you are getting what I mean here... well, drop me another comment if you dont, I can write a post on how to use cond. formatting with data...
@GWBE .. thanks, well, you answered your question 🙂 if you need 5 or more conditional formats, you can either do it in google docs or write some vba. Now, vba option would require running the macro / sub every time you change your data which can be painful. I am thinking of writing a generic macro that can take formats from predefined cells (range) and apply it to data range based on the condition outcomes, but not sure its worth the pain.
One more ridiculously simple option you can try is using find/replace dialog to replace with your own format for cells meeting the find criteria. This way, first you need to define format for a cell in another column (that would purely contain values like 1,2,3,4,5,6,7,8,...n, each one defining one condition) and the search for cells matching 1 and replace them with format. This can be useful incase you need to format a table with more than 4-5 colors and need it urgent and for one time... otherwise vba is the best option...
@everyone.. let me know if you still need a soln. for more than 3 cond. formats, I can work on that generic vba soln. in my spare time 🙂
Thanks man!
I have columns of numbers (a stat sheet). I'd like to highlight the max and min numbers (different colors) in each column to make the statistical leaders stand out. Is this possible using conditional formatting? I've used CF before but not to the extent you show. I appreciate any help/guidance.
Sorry...figured it out. =B1=max(b1...b9). This would determine if B1 was the max value from b1 to b9. Great site...
@Sixfive ... Its amazing you found the solution in 10 mins, I am happy my little site could help you in becoming more productive .. thanks for visiting PHD ... 🙂
hi I need to know how work in excel environment and to be professional in using excel
@hamo.. you are at the right place... just keep visiting phd, may be you want to start with posts in excel category - http://chandoo.org/wp/category/excel/
Love the tips you provided. It definitely helps, but it seems your information may be a bit advanced for first time users.
test
[...] This should be easy step, even if you are not a rock star of conditional formatting, [...]
Chandoo, I have spreadsheet architectural diagram/map of hardware in our datacenter (SAN Directors and Storage Arrays). Each SAN Director has multiple storage array relationships (Directors ISL'd to multiple Arrays). I am looking for a way to highlight an Array or Director and all it's relationships. Example: Enter in a serial # of an array and highlight that array box (serial# in cell), and the the SAN Director(s) and ISL's that are related to the Array I am doing the search on. Conditional formatting is limitted to 3 conditions and I need many more than that. Any help would be appreciated.
@Colebro: thanks for visiting PHD blog...
I am not able to visualize the problem you are having.. Do you mind sending some sample sheet to my email id... I can take a look at it during my spare time and get back to you 🙂 Otherwise, drop in another comment with a google docs link or images of the sheet.... so that I can answer your query...
Marked you in Delecious .
Thanks Chandoo for the excellent excel tips. And i thought i was an expert myself in excel !!
Alt +W+n has changed my life..... Just the thing i was looking for.
I have gone through almost all your post in category excel but couldn't find out the answer to this :
I receive data from different customers in a sheet. 25 sheets from 25 customers. This sheet is designed by me. Consolidation in a new workbook with 17 sheets is easy.
The problem arises when the customer changes the format of the sheet
Is there a way to allow access to certain cells only.?
Something better than Tools => Protection => Allow users to edit ranges.
Thanks
Conditional formating is nice, but I wonder if there is something "more powerful?" To explain, one limitation seems to be that conditional formating appears to be capped at three conditions. What if I wanted to have a sheet that tracked projects for 5 separate clients and each client have it's own color. I would love to be able to put in a specific client value to a particular cell and then that automatically put the entire project row in the corresponding color, but conditional formating (at least in the 2003 version) seems unable to do this.
So yeah, it is a great tool, but it's kind of frustrating because it makes me want to do more than it's capable of.
Hi the moment i saw this site, just thought had i been u. well thanks for whatever posted i learnt a lot yet atiny drop in the vast sea.
Actually i have been scearching for a portfolio, and i have got that but my problem is that all of those are single users and i want multiple investor with multiple agents. My work is to buy/sell shares through different agents for various investors and i am paid monthly for that. Can u please help me in this matter. Thanks-- Have a nice day.
@Sands: Welcome to PHD...
your query... I receive data from different customers in a sheet. 25 sheets from 25 customers. This sheet is designed by me. Consolidation in a new workbook with 17 sheets is easy.
The problem arises when the customer changes the format of the sheet
Is there a way to allow access to certain cells only.?
Something better than Tools => Protection => Allow users to edit ranges.
well, I use google docs spreadsheet forms [more: http://chandoo.org/wp/2008/07/14/use-google-docs-to-track-your-bucks-spreadsheet-forms-idea/ ] to get data from my colleagues in India, this works well since they dont have to understand the spreadsheet format to enter data, even though this is a good idea when you want to get data from multiple sources, not suitable for all occasions. If the consolidation is something that you do everyday, I would advice you to create a vba form to get the data, as it is one time effort and saves you a ton of time in ensuring the format is right. Also, is it possible that all the people who enter data can access a shared network location, if that is the case, you can just create a shared workbook and ask them to enter there.. that way .. you dont have to go through 25 sheets to fetch everything you need.
But I guess, none of this may help you.. so let me plea our readers for help.. if you can solve Sands' problem, a donut will be waiting for you 🙂
@Eric: hmm.. you seem to be stuck with the same problem our earlier commenter GWBE had. hmm, builds a case for writing a small vba macro to do conditional formatting if there are more than 3 cases. I am working on it and will share with you all soon.
Welcome to PHD 🙂
@Roughim: I am sorry, but I could not understand your query, can you rephrase it ...? Thanks for visiting my site...
[...] You can use similar logic to highlight after office hours (before 9AM or after 5PM) for time values. Learn these 5 tips to master conditional formatting. [...]
Never thought it could do such amazing things.Good job.
[...] Incell charting using REPT() spreadsheet is one of the easiest ways to include some data visualization capabilities to your excel tables without sweat. Click here to learn this technique of drawing incell charts. [...]
[...] 5 Microsoft Excel Conditional Formatting Tips [...]
how if i make a link with your site ?
thanks
http://www.excel4share.blogspot.com
@Saim Baig .. Welcome to PHD, thanks for the wonderful words. Subscribe to this blog to receive many more good tips
@Samsul... feel free to link to articles here along with a brief summary (if needed). Just copy the URL shown in the address bar and use it. You can also include a widget of this blog on your site if you wish... visit: http://chandoo.org/wp/subscribe/
This is a great help. I work at a hotel in Prague, and this will be a huge help for us as we use Excel constantly and this gives us a new way to organize the info so that the most important things can be read easily. Thanks for the tip.
Hi - I find the blog useful. I have a query.
I have a Column A with some data & in column B I need to enter some figures. If the figure entered in column B is not equal to column A, it has to be highlighted (I use Conditional formatting here). This is something I can do for 1 cell. However if I need to have this conditional formatting done across 100 cells, I am not able to C&P using Paste spl - format route as the figures in column A are not uniform & change.
How do I Copy paste the format wherein the format remains intact but allows the cell value to change?
@Uma,
change references in the formula used for conditional formatting in B1 from absolute to relative (e.g. =A1=B1 instead of =$A$1=$B$1) and you can simply copy down B1 using paste special/format.
[...] of the most popular posts on this blog is how to become excel conditional formatting rock star. Quite a few commenters there asked me if there is a way to get more than 3 (or 4) conditional [...]
[...] better than Arial. May be you like Georgia compared to Arial. But when you set out to create that gantt chart for your new project you have to manually change the font from Arial to Georgia everytime. No [...]
How do I copy and paste a conditional formatting formula but to auto update info.
For example:
Persons Name date
Persons Name date
Persons Name date
Persons Name date
Persons Name date
So, i have a large list of names. If there is no date next to the name, it highlight the name. (Basically telling me they didnt complete something). So I want to just be able to drag that formatting down. A1 now is looking at B1. But when i drag it, A2 looks at B1. I want it to look at B2. Do i seriously have to go in and format every cell?
@Melody: Welcome to PHD and thanks for your comments.
Ofcourse you dont have to reformat every cell separately.
First, you can define conditional format for cell A1 using some condition like this,
=b1=""make sure you write b1 and not $b$1. Then save the format by clicking ok.Now copy A1 and then select cells A2: A100 (or An) and paste just formats (menu > edit > paste special > formats only). You can use format painter as well. This copies conditional formatting from A1 to all the cells in the list and ensures that the references remain relative, thus from b2:b100.
Let me know if this doesnt help 🙂
This is simply exotic... Excellent use of excel.. Am impressed man...
I would be obliged if any one tells me about some more similar tips...my email is waseem_nawaz@hotmail.com... Carry on great work
@Waseem : I am happy you liked this. I have sent an invite to your email ID for joining daily PHD news letter. Please follow the instructions to get more tips everyday 🙂
I found this awesome blog,
I'll visit it again and again.
nice to know you,
greeting from indonesia
@IIP: Welcome ... 🙂
Hi Chandoo
Stumbled across your site twice in the last two days for two different excel reasons - thanks for sharing such useful excel tips 🙂
Quick question relating to conditional formatting - I have a sheet that compares a value with two others, shading only occurs when the value is higher than both of the other two. However, there are some occasions when the comparitive values are dashes and the conditional format doesn't get applied. It's like excel thinks a dash is 'higher' in value. I've tried changing the dash to a word, still no joy. Only way I can see to make it work is to make the dash a 0 but strictly speaking this isn't correct!
Any thoughts would be very much appreciated 🙂
Cheers
@Steven JP: Welcome to PHD blog. I am happy you liked the site 🙂
you can use isnumber() function to check if a cell has valid numbers or not.
for eg. instead of writing =IF(AND(D2>B2,D2>C2),"shade","leave")
you can write =IF(AND(OR(ISNUMBER(B3)=FALSE,D3>B3),OR(ISNUMBER(C3)=FALSE,D3>C3)),"shade","leave")
that way, you will see "shade" even if the cells B3 and C3 have non-numeric values like "-"
Let me know if this doesnt help. 🙂
[...] Become a Conditional Formatting Rockstar with these 5 tips 2. Excel can be Exciting - 15 fun things you can do in MS Excel 3. 25 Free excel downloadable [...]
[...] this might seem like a different challenge, it is infact same as the above techniques. You need to use countif() to compare first list’s elements with second list. How? that is your home [...]
@Chandoo - thanks for the additional advice 🙂
Hi Chandoo,
this is Swapnil from mumbai. I like this blog very much + the newsletter too. Thanks for it.
Just would like to suggest you to include some tips about pivot tables. ie apart from basic layout,formulae tips,pls suggest some new tricks/method if you have any..
Thanks man..
@Sawpnil: Thank you very much visiting the site and giving your suggestions. I will try to cover pivot tables in a post as and when possible.
[...] of the significant new features of Excel 2007 is improved conditional formatting. It has all the goodness of excel 2003’s conditional formatting and on top added new features [...]
[...] Become a Conditional Formatting Rockstar - Learn 5 cool Excel tips [...]
Plz look into this formula
@Karthick, I dont see any formula. Can you type it once again ?
[...] I couldn’t sit still after seeing his post. So here comes market segmentation charts or marimekko charts using, <drum roll> conditional formatting. [...]
Hi,
I am trying to write a conditional formula under the Formula Is category of Condition1. I want to compare 2 sets of cells. For example: =(c4>mb) AND (c4>D4). When I put the AND in, it gives me an error. Please help. Thanks.
@Michael: Welcome and thanks for asking your question.
There is no AND operator in Excel. You should instead use AND formula. Like this: =AND(c4>mb,c4>D4)
now it will work. Let me know if you still face some problems.
Btw, if you are having difficulty with formulas, try our online formula help tool : http://chandoo.org/wp/excel-formula-helper-index-cards/
Hi Chandoo,
Thank you very much for such a good site and useful tips and templates.
Please can tell how to create a calendar such the one you've made, I am talking about the mini one but I want to have the months vertically and the days horizontally. I tried to use the formula's you have provided, but end it in a mess. 🙁
best regards
Ilir
P.S By the way the info about the camera tool was so great. Well done for that. I didnot know it even existed there!
Hi Chandoo,
This is simply sooo useful for me I can't thank you the right way. BTW, I've noted that "bolding" incell graph detroy the scale.
OK, on one hand it makes them out like a a part of pie chart, but on the other hand, the scale is not respected at all...
I prefer the "normal" font for the graph part.
Cyril
@Cyril: You are the 99th commenter on this post... and that makes me the 100th .. in a way you gave me the privilege to be 100th commenter on my all time favorite post. I agree with you that using bold on in-cell charts can be misleading. This is partially due to how excel 2003 (and windows in general) handles the bolding of text. For eg. when you bold a certain portion of text in office 2007, you wont see such drastic increase in text length. That said, I completely agree with you that applying bold distorts the scale and can be avoided (or used very rarely).
Once again thank you so much for commenting here and sharing your views. Welcome 🙂
Happy my comment helps you in some way 😀
Great stuff Chandoo, I've been using some of these tricks for a while now - thought I'd give a little something back...
So here's an adaptation that lets you alternate the row colours to group data quickly - good when there's more than 4 identifiers.
Say this is my (sorted) data in column A:
Alice
Alice
Alice
Bob
Bob
Charlie
etc
I create a new column B, with -1 as the first value. Fill it down from B2 with this formula:
=IF(A2=A1,B1,B1*-1)
This fills the column with 1s and -1s, switching each time the value changes in A.
Conditionally format based on this column, and you'll get (eg.) all of Alices rows in blue, Bob's in green, Charlie's in blue, Dora's in green etc.
Better illustrated with pix of course...
Hi
I need Help!
Can Conditional Formatting be transposed into Graph Form? for example using a traffic light system.
Red 0 to 50
Amber 51 to 70
Green 71 to 100
I can do this in a table format, but it is required in Graph Form using a Bar Chart so it is automatically Green, Amber or Red
@Karen: this is not possible using conditional formatting, but you need to use multiple series to achieve this effect. I am planning to write a tutorial very soon on this... so please wait.. 🙂
Hi Chandoo
Thank you for the Info, I await the tutorial and thanks for coming back to me.
Hi Chandoo,
I'm new here and even if this is my first time reading through your tips I am very amazed and thankful of your wonderful excel usage.
By the way, I have a query. I have a worksheet that contains product names and their sales amount. What if I want to highlight an entire row where in the product name includes the word "TOTAL", there are a lot of rows with the word "TOTAL" plus a different product names per row. What formula can I use in conditional formatting for this?
Thank you in advance and I love your Site (^_^) More Power!
Hi Chandoo,
Fantastic blog, It's been really useful. I have a query though... I've got a column (B) containing different text data, some of which is in bold, some in normal font. What I want to do is create another column (A) where (for example) if B3 is in bold font, then A3 reads 'T', but if B3 is in normal font, then A3 would read 'C'.
Do I really have to add in the data manually to every cell in column A? Or is there an easier way with conditional formatting?
Any ideas? Thanks 🙂
Wow - you are such a badass Chandoo! Thanks for sharing with the community!
Hi Chandoo, well-written info - great tricks and tips. Question: do you know of a way to provide greater transparency into the Conditional Formats currently in effect in a worksheet? I use Conditional Formats a lot, and it would be nice to have a relatively easy way of viewing them, without having to go into each cell...
Thanks again for your great posts!
[...] http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ [...]
Hi Chandoo,
I have been a regular visitor of your great site in 2008 and lost track somewhere in 2009..am back again. I remember seeing a post where you told how to put in sybols like check mark or a cross mark using conditional formatting..I cannot find it now. Can you direct me? I want that instead of having Yes and No in cells, I shall get check mark and cross mark
[...] format. (Here is an introduction to basic conditional formatting if you need one of those, and a bit more advanced here) VBA cross-cursor with conditional [...]
Great article on conditional formatting. This is even more useful than the more basic page which I liked too.
I have tried to extend the conditional formatting using circular high-lights around the selected cell, and it's pretty neat even though I can't think of any commercial applications...
http://blog.corality.com/2009/03/vba-and-conditional-formatting-in-excel/
What do you think? Can you think of an application?
@Loves: I am sorry you had to wait almost 3 weeks to see an answer, but yes, you can do this using conditional formatting. There is a formula called FIND() which will tell you if a certain text is found in another text.
You can use it in CF to highlight the row. For eg. =iserror(find("total",$c1))=false would return true whenever the cells in column C contain the word "total"
@Rachael: Welcome. Unfortunately, there is no built in way to findout if a cell's content is bold or normal. You can use VBA to find that out. But it would be cumbersome. A better solution would be to use some other way to determine (for eg. if you are bolding all the cells with above average sales, then we can use formulas to find that)
@Rob: In excel 2007 they have introduced a conditional formatting manager which provides you a lot of information on all the CFs in a workbook. In earlier versions, you still need to do a lot of guess work.
@Amit: hmm.. I dont remember such post. But I have written a few on using symbols, you can see them here: http://chandoo.org/wp/2008/08/07/display-smileys-excel-dashboards/
http://chandoo.org/wp/2008/08/21/display-symbols-excel-chart/
@Rickard: Welcome. I like the first use, generating cursor pointers using CF. I think it will be useful when you work a lot with spreadsheets and need to know where cursor is...
Btw: you have a very good blog...
@Rachael If you were to implement a solution based on the bold then maybe populate a new column with the info based on the text formatting (bold/normal => 0/1 ) and then use the new column for the conditional formatting. Ideally I would recommend not using bold to drive any mechanics but rather use the 1/0 as an input and then use that to feed the conditional formatting and the bold/normal.
Hi Chandoo!
Great stuff Chandoo, I’ve been using some of these tricks for a while now. Using symbols triggered by conditional formatting is petty nice to structure dashboards. I use this very often combined with an adapted color palette.
But the problem is, that using visual signals from the windings font or other font families may look neat on the screen,.... but printed..... ??? - Different printers replace these signs by their own font substitution.
Any ideas?
Have a nice day, and keep rocking 😉
Michael
Great blog.
Here is my issue with conditional formatting:
in a row (in this case row 10) I have a column with a value to determine the color of the cell (=" =$C10=""x"""); this is the first condition, the second condition is =($B$5-D10)>$C$4 which turns the cell to bold if true.
Problem is if the first condition is true it never evaluates the second condition; is there a way to make it so two (or three) conditions can be applied to one cell?
Thank you
@Francois: Welcome to PHD. Conditional formatting is evaluated sequentially and stops at first match. so if you want RED+Bold, you need to define a combined formula for condition 1, and only red as second formula. Then it works. You can use the excel function AND() to evaluate 2 conditions.
Chandoo,
AWESOME TIPS!!!!!!!!!
I need some help (been told that before) ... I'm trying to use your tip of insert/symbol in order to get up, down, and left/right arrows in a cell, but I can't get it to work the same. When I do it, it inserts the symbol but it only shows up as an arrow if I format the cell as Wingdings 3. otherwise it's a character like P. However I downloaded the sample s/s and in those formulas (on my machine anyway) there are actually graphical arrows and a dot or bullet in the formula surrounded by quotes like this: =IF(D2="G","?", IF(D2="Y","?",IF(D2="R","?",""))) and those arrows and dot remain regardless of the font for the cell. Can you shed any light on how I can actually get the arrows in there instead of alpha characters and formatting it Wingdingst??? Please???
THANKS IN ADVANCE!!! And thanks for a WHOOP@$$ SITE!!!!!
Well @#$%@#%$ ... that formula sample I pasted above converted to characters too ... sorry ... didn't know it'd do that ... looked ok when I pasted it.
@JT: the default font (arial or calibari etc.) have few arrow symbols. You can find them from insert > symbols dialog. I use them as far as possible. But in some cases you need special symbols that are only available in wingdings etc. In that case you need to change the font.
hi chandoo, i have another challenge for u 😉
consider that i have 4 rows x 5 columns of numbers:
Line1: 1 3 5 7 8
Line2: 32 54 22 55 86
Line3: 0.1 0.2 1.2 0.8 0.9
Line4: 9 5 4 1 1
for the time being, i've been applying conditional formmating to EACH line; highlighting the MAX value of each row. I used format value based on rank (Top 1).
For example above, numbers that are highlight are: Line1 is 8, Line2 is 86, Line3 is 1.2 and Line4 is 9.
how do i simplify the process so that I only select a range of numbers (above) but for formatting each line instead?
REason that i'm asking because i have tons of similar data to format and it's not feasible/practical to set rules to each line (just to highlight the MAX value in it)
thanks!
@cybsych,
I think I have a solution for your problem. To explain it properly I have entered your numbers in A1:E4.
The conditional formatting would then be
"Formula is" : =MAX($A4:$E4)=E4
Just make sure the formatting is ok and you shouldn't have any problems.
The approach is pretty similar to this example:
http://www.navigatorpf.com/training/tutorials/dynamic-cross-cursor-in-excel-vba
Regards,
Rickard
http:blog.corality.com
hi Rickard, i think u got the wrong idea (sorry!) 🙂
considering the range A1:E4 above...
i'm thinking of selecting this range, but in the conditional formatting (formula), it will highlight the MAX value (cell) in EACH ROW (i.e. each row will not interfere with the other rows)
the target result for each line is:
line1 - 8 will be highlighted
line2 - 86 will be highlighted
line3 - 1.2 will be highlighted
line4 - 9 will be highlight
what i'm doing now is very manual intensive: select line1, condition format it ... select line2, condition format it ... select line3, condition format it .. select line4, condition format it ....
i have hundreds of such lines 🙁
@Cybpsych... the method Rickard suggested should work when you select all the cells in range A1:E4. The formula using relative reference for rows and absolute reference for columns. Just change the range to your actual range of cells and it should work smoothly.
Let us know if you face some problem.
@cybpsych, yep, Chandoo's comment is right. Just select the whole range before applying the conditional formatting. Drop me an email if you want me to forward the workbook.
Firstname.lastname@corality.com
hi guys, i found out what's the problem ....
i think i forgot to mention that i'm sing XL2007...
problem is this: if the condition format's formula is =MAX($A4:$E4)=E4, then Line2-to-Line3 are all highlighted.
somehow, i accidentally solved this ... when i pasted the formula, XL changed it to =MAX($A1:$E1)=A1 ... then all the rows are *properly* highlighted 😉
well, that solves the problem 😉
thanks guys! you've been very helpful! 😀
I have a problem figuring out a way to conditionally format the following.
I want colum (v+) to highlight if (visitor) scores by more than 2
I want colum (v) to highlight if (visitor) scores by 1
Also the same principles for the (over) (under) columns
example:
V+ V T H H+ OVER # UNDER
1.70 1.40 3.00 2.20 2.60 1.80 5.5 1.60
hi
m from mumbai
need to learn ms excel
i work for an investment co my clients ask for their weekly investment presentation , i really need to know how to do it .. how to give them report regarding their shares .. they want me to give a chart which shows them their buying cost ,average cost (if bought at diff price) current rates , value, etc etc..
i also want to learn excel in deep which would help me in my future
sir can u please help. my job needs these art very much now.
@Jonathan: I am sorry, but I missed your query. Did you manage to solve it. Otherwise here is the solution.
You have to apply conditional formatting to each column separately. For eg. Select column V+ and in the formatting rules you can mention something like, >=2 and set the formatting as you prefer.
Just repeat this process for all columns and you will be able to highlight based on the values.
@Tejas: Welcome to PHD. You have come to the right place. I would recommend that you sign up for our email updates or RSS feeds so that you can receive new posts as they are up. Also, I suggest reading the excel and charting archives: http://chandoo.org/wp/category/excel/
http://chandoo.org/wp/category/visualization/
You may also want to check out some of the other popular resources for excel to stay up to date. You can start with http://peltiertech.com/WordPress/
http://blog.contextures.com/archives
Feel free to ask your questions here and I am sure me or some of the regular readers will be happy to help you.
Is it possible to use conditional formatting with vlookup? I would like to format all the values in column A based upon a textstring in column B.
@DeskDiva: Welcome to PHD and thanks for asking a question. Yes, you can use vlookup with CF. I am not sure if I understood your question correctly though. Do you mean to say, you want to highlight all the cell in column A that have a string in cell B1 or something like that?
If that is the case, you can use find() or search() formulas and not necessarily use vlookup(). In the above article, the tip #4 talks about this.
If you need to use vlookup(), make sure you use absolute references (like this: $B$1:$C$10)
Let me know if you have more questions...
Hello Chandoo and hi to everyone else *waves*.
Been using your advice now for a few months, thank you for the awesome site and help you provide, and wanted to post up a question.
With respect to "2. Creating a quick project plan / gantt chart using conditional formatting:" you have posted above, can you or any of our readers think of a simpler way to create a gantt chart, in excel, to measure work done during a day. The data would need to be tracked on a "minute by minute" basis. I guess what it comes down to is trying to reduce the number of columns - each day is 8 hours, but start and stop at different times. I just feel that 480 colums seems like a lot to me.
Any advice and or tips are appreciated.
@GaryWolf... welcome to PHD.. *waves even faster*
You can try using REPT() to create incell bar chart that prints spaces until start time and then prints | (pipe symbol) for the total minutes spent working on a task. It is very simple to develop and works smoothly. Some examples are here: http://chandoo.org/wp/tag/in-cell-charting/
Another option is to use actual bar chart, that way, you can just make one bar chart and forget about the nitty-gritties about the columns etc. Here is a useful tutorial:
http://chandoo.org/wp/2008/05/20/gantt-in-60-seconds-churn-out-an-excel-project-plan-in-no-time/
[...] we will do some conditional formatting (ahem!) that will highlight a particular cell in the [...]
Awesome tip Chandoo, thank you.
I'm really liking the the REPT function. Ha ha , I have used it for may other graphs, surprised I did not think of it earlier. I am familiar with it's normal, and easy, use.
I was wondering if you might assist in a REPT formula that can creat a Gantt like effect using a start number and an end number.
eg. If my total range was 0-10 but the start was 3 and the end was 7 how do I get the gantt like efffect to fill in spaces for 0-2 then fill in 3-7 with the "bars".
I have a formula that can put the spaces in, but only get it to mark either the start OR the end, but not both in the same furmula.
I hope this makes sense.
Thank you again.
Hello, actually I think I found it ... maybe a bit sloppy, but it works for me. Just tooled around with the formula a bit and voila.
A little concatenate can work some magic.
Here it is:
(FYI - 100 is the max value on my report)
=REPT(" ",start)&REPT("|",(end-start))&REPT(" ",(100-end))
Thank you
@Gary.. kool. You are right, using REPT() with concatenate operator does the trick. You can see a more complicated but fun example of this technique in the in-cell dot plots post: http://chandoo.org/wp/2009/06/10/dot-plots-in-cell/
Thank you for your assistance Chandoo. Got it working perfectly witht he dot plots too.
Fyi: it's Gray not Gary (^.O)
Peace.
Hello chandoo,
I love this blog ever since i discovered it
barely a week ago
my problem is that i use excel 2007
which makes the above
a little difficult for me to demystify
any assistance?
hii,
i want to make an attendence sheet and only 2 option present or absent to be there.
If a person is absent in front of its name cell we should have only 2 option to fill either absent or present and if present it should automatically fill with green and if absent it should automatically fill red
how to do this please suggest.
Hi.. I am a die hard fan of excel. I haven't learnt it from anyone... Just hit and trial method... a little help from here and there.. from internet etc. Till now I have found whatever I required, related to my work, as and when the need arose. But I certainly believe that having thourough knowledge of all functions is always useful. What I do is that I get some sample spreadsheets (for anything I need to learn), study them, practice them and use them in my work. But I really really wish to learn excel in a professional way... is there any way??... I am sure you would be having some way...!
[...] Incell charts w/ Conditional Formatting [...]
@Sandeep... Thanks for asking this question. I am planning to start a training program on this site for people like you. It will cover all important areas of excel and help you become an expert in using excel. I will update you once the program is ready.
[...] means you don’t need to use conditional formatting or manually format alternative rows in different [...]
[...] Excel Conditional Formatting – 5 tips and tutorials [...]
Chandoo, thanks for all your work on this site. I am trying to figure out how to identify clients of mine in a national report of company clients. I am not new to Excel but still a novice. I came to your site to learn about conditional formatting to help me accomplish this task and it does not seem that this will be able to pull this off ether. Am I wrong? Or do you know of something else I can try?
@Jim.. Welcome. You can do this using conditional formatting. The technique is fairly straight forward. Assuming all your clients are listed in the named range jimClients
and assuming your national report has client names in the range A1:I2000, select this entire range and apply conditional formatting by rule like this,
=countif(jimClients,A1)>0
and set the formatting to the highlight color you wish.
This should highlight all the cells in rane A1:I2000 when the value is in jimClients.
I want Apply Excel Conditional Formatting to a column with dollars and units. All depending of the total Sales if ($) >= 50,000 Green
and < 50,000 red then I have to color their units as well.
Can you please help.
Thanks
@Alassane... Welcome to PHD and thanks for the comments.
Here is how you can do this. Assuming dollars are in range A1:A10 and units in range B1:B10, Select the range of cells and open conditional formatting dialog.
Set rule 1 as "Formula is" "$A1>=50000. Set the formatting as green fill color.
Repeat the same but enter "$A1<50000" and color as red for rule 2.
Now you should see the colors. Let me know if you have any problems.
The trick lies in understanding relative vs absolute cell references. We have made the column (A) absolute and row (1) relative. So even when applying formats to column B (units), excel checks the dollar amount (col A). See more here: http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/
I am trying to apply conditional formatting to a cell. I have three conditions : if the cells contents equals "H", fill the cell with one colour. If the contents equal "SL", fill it with another colour. If the contents equal "PH", fill it with another colour. "SL" and "PH" work fine. I have done nothing different with "H", but it won't change colour. There are some cells on another page where "H" does fill in. When I copy the cell, the "H" stays in that colour. If I then delete the "H" and re-enter it, the cell stays white. The letters are selected from a drop down list, if that has any bearing. I'm using Excel 2003, and this is on a work computer, so I can't update it in any way. Please can someone help?
Hi Chandoo,
The data is not in different column, it is in the same column for instance:
Sales $25,000
Units Sold: 201
Sample Units: 20
Then Another one same column but different product
Sales $55,000
Units Sold: 201
Sample Units: 20
so on. My boss does not want to change the vertical view to horizontal.
How can I do color the one >=50,000 and less than 50,000
Thanks
@Jessica: seems strange. Here is what you can do. Select all the cells where you wanted CF. Go to Menu > Edit > Clear > formats. This will remove all the formatting done on these cells. Now go back to conditional formatting dialog and set the rules 1,2 and 3 to match the text H, SL and PH. This should work..
@Alassane.. oh.. is the data strictly 3 cells per item ? then you can use CF with formulas to do this. You need to use either OFFSET or INDEX to do this.
For eg. assuming the values are in A1:A30, you can apply the CF with this rule...
=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/3)*3,0,1,1)>=50000
Remember, A1 should have dollar amount, A2 units, A3 sample units. If you have 'n' cells per item instead of 3, just replace the /3)*3 with /n)*n
All the best.
Hi Chandoo, thanks for the reply, I seem to have solved the problem, but I don't know why. The whole issue hangs on the letter "H" (at least, I have experimented with various letters and phrases, and all else works except for "H"). So I then wondered if Excel couldn't 'see' the "H" because it thought it was something else. I have set the cells to "Text" and this appears to work. What I can't explain is why no other letters affect it thus - could it be because "H" is used in connection with a short cut (at least, I think it is)? I haven't experimented with, for example, "F" yet. When I regain the will to live, I will try your method, it would be interesting if that solves it as well, and seems to be a more 'clean' solution. Tell me, has anyone else had the temptation to shout, "Stupid thing!" at their Excel spreadsheet?! 🙂
HI Chandoo,
I try it but it is not working
I have to give you an example
Fill Size + Configuration
Data 5 [1] 20 [1]
Sales $35,104 $116,851
Local Units Sold 721 3,175
Free Goods Units 7 4
Sample Units 2 4
Another one so on
Sales $72,976 $25,000
Local Units Sold 538 530
Free Goods Units 3 1
Sample Units 2 2
It is in many colums and different rows on the pivot.
The sales depend on the fill size of the goods and the products.
Again I want to highlight in green the sales >=50000
and in red sales <50000
Thanks for your effort...
very useful, thanks a lot
@Alassane... can you upload the file somewhere and give us the url.. I am not able to visualize this.
@emre... You are welcome
Chandoo,
Thank you very much for your help,
I did it.
Thanks
@Alassane.. I am happy for you.
[...] You can also highlight expenses above certain pre-defined amount. For this, Sovan uses Conditional Formatting. [...]
Hi,
This query refers to the 5th part of the above post.
Suppose the value and % is to be displayed with one decimal place in the dashboard and one or more of the values or % have zero after the decimal point for e.g., in your illustration, cells C11, C13, L10,H12…
To display values with one decimal, if I use instead of
ROUND(C11,0) & " " & IF---
this:-
IF(OR(ROUND(C11,1)=INT(C11),(ROUND(C11,1)=INT(C11)+1)),ROUND(C11,1)&".0",ROUND(C11,1))&” “& IF----
the values get displayed with one decimal place.
Whereas, in the % panel, cells like L10, H12 have values, whose formula if is changed from
Round (cell reference *100,0)&”%”&IF---
to
Round (cell reference, 3)*100 &“%” &IF ---
the values don’t get displayed with one decimal.
I’m struggling for the past half hour to make something similar to yours (for someone who is bent upon having one decimal place in the %) but with no luck till now…Would it be possible to advise?
@Savithri.. try this: =ROUND(cell ref*100,1) instead of =ROUND(cell ref*100,0)
Thank you but sorry - It doesn't work. Pls ref value in cell H12 in your illustration. It is 73.95% which, when rounded off, displays 74% and not 74.0% as desired.
Wherever the %s on rounding off becomes #.0, when concatenated with "?" or "?", don't get displayed with one decimal.
(I am trying to display EVA (nominal values) & EVA rate of return %s where the %s are not that high and I'm told that the decimals are significant).
@Savithri... Thanks, I didnt realize that earlier. You can try =TEXT(ROUND(H12*100;0);"0.0"), it forces the format thru TEXT formula.
A Million thanks. It never once occurred to me to use 'text'.
[...] used conditional formatting to zebra line the [...]
Is it possible to use conditional formating in bar/columns charts? I mean if you have a 10 column chart and apply conditional formating to them:
eg. Values > 70 to be green coloured charts
>40<70 to be yello and < 40 to be red? I know its possible to change colours manually, but is it possible to apply conditional formating to columns / bars?
[...] celebrated the one year anniversary of “conditional formatting rockstar” post by writing 5 more posts on excel conditional formatting. The series started with [...]
[...] Excel Conditional Formatting – tutorial and examples [...]
@Pushkar... You have to use different chart series to achieve that.
That really helpful...really nice and cool
I've been using the REPT command with "|" to create a % completion bar. It looks great on screen but when I print, the bars are shifted and it no longer looks as it does on screen. Any solutions? Thanks!
I think it's an Excel problem. I have the same issue and I think it effects Word as well. To determine this, open Excel and place a circle, then hit print preview. If the circle looks slightly flattened (oval), you have the same problem I have been experiencing where Excel "flattens" the Y axis or "stretches" the X axis. This also effects the REP("|".... stuf you are trying to do.
[...] http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ [...]
Thanks so much. My Excel charts look much better and are far more effective with the conditional formatting tips as described.
[...] needs a simple conditional formatting and the excel spreadsheet function PERCENTILE. The syntax of this function is PERCENTILE (array, [...]
Hi, regarding the Gantt example, what formula would I need to use to automatically change the color when a task is finished or the date is complete?
Thanks
Hi Chandoo
I'm trying to solve the following conditional formatting problem:
In the table below I'm trying to:
- get the "AGE OF BUILD" by using the following conditional formula is: =TODAY()-A1>730, where >730 is the number of days (2 years) before the "BUILD" needs to be inspected. The "DATE OF BUILD" cell or the "AGE OF BUILD" cell needs to change colour to yellow.
Whichever cell changes colour, that same cell will also need to change colour to orange when the number of days reaches 365 before date of inspection.
When the same cell reaches the date of inspection (every 5 years from date of build) it turns red
DATE OF BUILD AGE OF BUILD
2000 10
2001 9
2002 8
2003 7
Andrew
@Yip... You can use a formula like =completion_date<today() to see if the date is past. Of if there is a column with completion status (say done activities are 100%), you can use that.
@Andrew: I understand that you need 3 colors - yellow, orange and red. But I dont understand what conditions you are looking for. Can you pls. explain?
Hi Chandoo
Thanks for the response.
Conditions
Each vehicle has a date of build and they are all required to be inspected every 5 years until they are decommissioned. To plan the inspections, and there are a lot and they are big, I would like notification in advance with a simple color change to the cell (Date of Build). The first notification to be 2 years prior to the inspection (yellow), the second notification to be 1 year prior to the inspection (orange) and the third notification to be the year of notification (red - inspection year).
Now the calculation becomes more involved. The inspections have to happen every 5 years, therefore the color changes need to occur as above - 2,1,0 and at the following intervals - 5, 10, 15, 20, 25, 30, 35, 40, 45 etc.
Thanks
Andrew
@Andrew ...
very good question 🙂
Assuming your dates of build are in A1:A10,
Select A1:A10, go to CF > Add rule > Formula based rule
write the formula =MOD(YEAR(today())-YEAR(A1),5)=0
Set color as Red
Select A1:A10, go to CF > Add rule > Formula based rule
write the formula =MOD(YEAR(today())-YEAR(A1),5)=1
Set color as Orange
Select A1:A10, go to CF > Add rule > Formula based rule
write the formula =MOD(YEAR(today())-YEAR(A1),5)=2
Set color as Yellow
This should work.
Sir Chandoo,
You are a genius. I have learned a lot from your site.
I love your graph where you have the up and down arrows beside the numbers to show if they had gone up or down since the last month, however when I use them it takes away the numbers after the decimal in the sheet. My sheet is formatted to show two numbers after the decimal. why does it take these numbers away? Most of my spreadsheets are either numbers with at least one number after the decimal or percentages with at least one number after the decimal 🙁 (I have Excel 2003)
BTW, I LOVE your page and all the great Excel info!!! thanks so much for doing this!
Hi Chandoo,
Wonderfull site! You explain very well.
Used a lot of your examples in my sheets.
@Meenie.. Sorry I took so long to get back to you. Are you still facing this problem. It could be due to ROUND(...,0) thingie. You can set it to ROUND (...,2) to show 2 decimal places.
@Hallo.. You are welcome Hallo. Thank you so much for the comments.
Hey,
Regarding Point 3, the highlighting. I can't see an answer in the comments. How do you make the REPT | 's Red. I'd like the highest value to show red. Any ideas? thanks
Ben
[...] During February 2008, one of my articles was picked up by Lifehacker (this one – http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ ) and I received a flurry of visitors. This encouraged me to write more and connect with a wider [...]
[...] Use conditional formatting to highlight relevant stuff: If you are going to demonstrate scenarios in your model, you can use conditional formatting to highlight scenarios. You can also use CF to bring key values / areas to user’s focus. (tip: become a conditional formatting rockstar) [...]
[...] Problem inspired from this post on Chandoo.org forums. Don’t cheat. PPS: Here is some beautiful help on conditional formatting. Spread some love,It makes you awesome! Tweet [...]
[...] link [...]
Hi Chandoo.. ur awesome... what a life saver... luv u so much.....
Dear Chandoo!
I have problem with, when i use conditional formatting to highlight some text or numerals. Even after saving the file, as soon as i close the file. again i have to do the same to highlight in a same way. I am not able to keep the text or numeral highlighting forever. even after save and close the file.
Please help me to get rid of this problem.
Regards,
Praveen.D
@Praveen
Does your conditional Formatting have times involved? Your CF's may be invalid at the new time the worksheet is opened?
Is it linked to other files that could change or be removed/shifted after you save? There is notes on the web about strange things happening to CF's when links are broken/removed in XL 2007
Are you saving as the latest Excel file format ie: File.xlsx or File.xlsm or File.xlsb or an older format File.xls ?
Do other people have access to the file after you close it ?
Hi Chandoo,
I have to format the entire row on each change in a particular column. I need to highlight the rows with different colours for each change in 'Name' Column, i.e. rows containing James should have a colour, rows containing John should have another colour, etc. Please help he how to use conditional formatting in this context.
Please find below one example.
Tax Year Name Amount
2008 James 135
2010 James 70
2009 John 146
2008 Raju 120
2008 Ravi 100
2010 Ravi 160
2010 Ravi 130
2009 Srinivas 85
2010 Srinivas 80
2010 Subash 115
2010 Subash 110
Best Regards,
Aby George
Aby
select Rows 2:Row X
and apply this formula to the conditional Formatting
=MOD(SUMPRODUCT(--(($B$1:$B1=$B$2:$B2)=FALSE)),2)
[...] You can highlight alternative rows in different colors using conditional formatting. [...]
Chandoo, I need some help. I'm trying to utilize your example 5 as a basis for displaying trending with currency (e.g. $356,000), but I notice that the formatting is lost in the process (e.g. 356000 is displayed). How can I fix the function to include this formatting? Thanks.
@Regalcom: You can use TEXT formula to format the number, like =TEXT(number,"$#,##")
Thanks. Worked perfectly!
[...] 5 Ways to become Awesome using Conditional Formatting [...]
Hi Chandoo,
Greetings from Pakistan, mate I have been using your site to learn excel for about a months now but never commented before. I only have one thing to say, you are the ULTIMATE GURU of excel. Keep up the work.
Ashher
@Asher.. thank you so much for the kind words 🙂
just a quick question. How if I have a particular cell that has a conditional formatting, and then I would want to extend it to the rest of its row, which would mean that there will be a long horizontal line that stretches across when a condition is satisfied. It's kinda like an indicator to show me when the figure of the cell hits below 0.
Sorry if this is too Noobish.
🙁
In #5. Creating dash boards using excel conditional formatting, how are the symbols added? Using Excel 2007, when typing the formula the insert features are grey.... can't select the symbol button. Also, pasting the text will not carry over the font in the formula bar.
Thanks in advance
@Hellomoto... You can insert symbols (http://chandoo.org/wp/2010/10/11/insert-symbols-in-excel-howto/ ) in to a blank cell and then make references to it from formulas. Finally, change the formula cell's font to wingdings etc. to show the symbols.
Hi Chandoo
I learnt about Table features in Excel 2007. I can't understand why they work sometimes only and fail otherwise. After reading a column, I tried using the conditional formatting using the formula:
=IF(AND(Table1[[#This Row],[Qty Available]]<=Table1[[#This Row],[Reorder Level]],Table1[[#This Row],[Qty On Order]]=0),TRUE,FALSE)
Here, sometimes Excel accepts it but shows no formatting as result. Sometimes it keeps saying Error. I tried using the same using Formula Evaluator but from the first step it starts giving errors.
Do you have any ideas?
Your page is good and informative, and others too contribute to make it effective. Keep up the good work.
wow been looking for the easy tutorial and this is it.
thank you so much
love from indonesia
Hi,
If I have applied some conditional formatting to a range is there an easy way to "find" all cells that meet that criteria. For example, the cells backgroud might be highlighted red if criteria true but the standard excel 2003 find format does not seem to find those cells. Thanks
hi Chandoo,
i want to know how to protect a particlur row or column in ms-Excel 2003. please tell me how to do.
[...] without Project. Other users have created customized templates for easy use, while experts offer conditional formatting masterclasses. For free. Because they understand that if you’re going to use any tool, you should take the [...]
Neat tips
struggling to remeber everything i'm learning on this site!
Chandoo,
First off... this is absolutely awesome. I am pretty good with formulas and conditional formatting (by no means perfect or an expert). My question is this:
I got the conditional format for alternating row and column colors, is there a conditional format to make it a checkerboard whereas the cell A2 will remove either the conditional for the row or column and then alternately to A4, B1, B3 etc?
Thank you for all your answers and your time in helping us out with this extremely powerful program. I havent checked yet but do you also have an Access version (I personally like access, much more powerful, but nowhere near as good with Access as Excel)?
@BobR: Thank you so much for your comments.
Very interesting question. You can combine MOD, ROW and COLUMN formulas to get this. See the example I made to understand how to setup the CF rule.
http://chandoo.org/img/playground/checkered-board-cf.xls
@BobR
Try this one as a Conditional Formatting formula
=ISODD(ROW()+COLUMN())
Very interesting and simple solution Hui.. 🙂
Hui...,
Outstanding. That is exactly what I was looking for. I thank you very much. I changed it to a ISEVEN because I wanted the first cell to contain the formatting, but the conditional format is what I was looking for.
Chandoo,
I tried yours as well and it works great. I have a few ideas on how I can use that in one of my other spreadsheets for just a particular range of cells, and not the whole workbook. I must say I was close in the formula I was building except I was using , instead of * and kept getting errors. Thank you for the little lesson.
Hi Chandoo,
I am just now experimenting with conditional formatting. Your article is helpful. I am able to use it successfully when I am just looking for a value (I wanted one colour when the count of several rows was 0, and another colour when the count was 1).
Now my problem is that in one column I have calculated the maximum value for several columns. I need to compare it with the next lowest value. I've used MAX and LARGE for my calculations. In the "large" column I'd like to highlight the entries that are exactly equal to the maximum value (in other words, when there is a tie. I've tried to use something like "cell value is" "equal to" , then whatever I put into the next box, it will not work. So, for example, in row 2, my max is in B50, and my large,2 is in B51. I've clicked on B51 to input the conditional formatting. What should I be putting in the next box?
Thanks for your assistance.
Hi chandoo
This site is great and helful as well! It’s helped me a lot to have a new idea and technique that I can use to make me more fast and accurate on my reports.
I have 1 question about the Gantt chart. Becaused on you’re example its look like this is for each month only,
What if I start this month up to next month, and that next month I need to use a new sheet?
This is conflict on the previous month because we put the start and end date.
Is there any tip that you can advise about this one, or there is no other solution?
Keep up the good work, this one is great great great!!!!
Hello once again Chandoo, I am hoping you can help with why the following two conditional formats are not working:
=IF($L$8="Sold","$C$10+$K$8","")
=IF($L$8="Own","$C$10+0","")
Basically I want the cell that contains the cash balance in a spreadsheet to do nothing if the stock is still owned by me and to add the value of what I sold it for to the balance once sold by just putting in the word
Thinking as I am typing this I really do not need the "Own" condition, so will actually take that out, but could use some help with the "Sold" one. It is probably something so simple and I am just not seeing it.
Hi Chandoo,
I find your site marvalous. I need your help. I will be thankful if I get a solution for the following from you. I am making a spread sheet in XL to enter various data into the sheet. I have the following question.
"In the xl sheet if the letter S is typed in the first colum of any row, the font colour should change to red in rest of the cells in that particular row. Give me a solution, pls.
With regards'
Thaseen
@Thaseen.. welcome to chandoo.org and thanks for your comments.
Do this:
1) Select all the columns you want to format
2) Goto conditional formatting > new rule
3) Specify the rule type as Formula
4) Use the rule =$A1="S"
5) Set the formatting as you want.
This should work.
what should i do if i want a whole word change in red if it start with a letter "S" at any cells of my worksheet. Plese reply soon.
Dear Chandoo,
I have found the solution, i.e>put this formula in formula bar =LEFT(A1,1)="S", and after then select the whole sheet in "applies to".
Was looking for a simple conditional formatting, and got through your site....so many appreciation encouraged to ask you this.
Highlighting alternative rows / columns in tables - saw this. Yet unable to solve my concern.
If I have to color row, depending on "Yes" or "No" value or "Null" value in the cell, what should be the formula. Like one color for "Yes" and no color for "No/Null". Doesn't matter which cell would have that in 1 column.
Now, if column next to it again has again the option to choose Yes" or "No" value or "Null" value, how would that supercede the color pattern applied by previous columns cell values.
I want this for both Excel 2003 & 2007.
Thank you.
@Rupesh
Applying a Conditional Format to a range of rows (Lets say 10:20) using an equation
=(C10="Yes")
and applying a format will solve your problem
.
If you want to check any cell in a row has a Yes use something like
=COUNTIF(10:10,"Yes")>0
[...] http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ [...]
I'm not sure if it has been mentioned before (I didn't read through all of the comments), but under the section for conditional formatting column (rows) background, you might want to specify in the equation "=MOD(COLUMN(),2)=0" that the place where the "2" is, this designates how many columns/rows are in the alternating set, and where the "0" is, this designates which column is being effected. In other words, it isn't only based on whether the column/row number is even or odd.
Therefore, if a person has a series of 4 repeating columns/subcolumns (such as the case when multiple projects are displayed as columns and task owners are sub-columns under each project), the equation to effect the first sub-column would be "=MOD(COLUMN(),4)=0" and the equation to effect the next one would be "=MOD(COLUMN(),4)=1" and the next would be "=MOD(COLUMN(),4)=2" and the next would be "=MOD(COLUMN(),4)=3".
Oh, and that last column works best if the columns are consistently repeating. Otherwise, you would need to use IF statements...
Hi, I have an issue, would be really help full if you fix it.
-if F1= "done", then current time should be displayed in G1.
I was able to do this just for one cell, but am not able to do it in conditional formatting/ or for all the cells.
I am using excel 2007, PLEASE HELP!!! have been tryin a lot but no success. Your help will be greatly appreciated!!
Hi Chandoo,
This may be a little elementary but here is what I am trying to do...
Making a time card calculator with weird parameters.
conditions: If worked at least 8 mins of a quarter hour then round up to the nearest 1/4 hour.
If worked 7 mins or less then round down to the nearest 1/4 hr.
The cell with the total is formatted as number with .00 decimals like 8.10 or 7.90
What should this formula look like?
@Brandon...
Thanks for your question.
Assuming A1 has the time in the format you mentioned (8.10, 7.90),
In B1, write =ROUNDUP(A1*4,0)/4/24
And then format the cell (select B1, Press CTRL+1), and use custom format code - HH:MM
This should display the time rounded to nearest quarter hour.
Dear chandoo,
man you r a genius, I think youa re the only person who has used excel in full
[...] Chart Templates 6. Excel Tutorials for Beginners* Advanced Excel Tricks 1. Excel Dynamic Charts 2. Learn Conditional Formatting 3. Making Dashboards using Excel 4. Project Management with Excel 5. Working with Excel Tables [...]
[...] 5 Tips to become a Conditional Formatting Rock star [...]
[...] it was also very time consuming. I knew there had to be a better way and there is! The below from chandoo.org looks complex, but it isn’t. I suggest you print out the below and try it the next time you [...]
[...] it was also very time consuming. I knew there had to be a better way and there is! The below from chandoo.org looks complex, but it isn’t. I suggest you print out the below and try it the next time you [...]
In this formula, I would also like it to display decimal points (2) and the $ sign.... is it possible, and is there a way to make it a conditional format?
=ROUND(C10,0) & " " & IF(C9<C10,"?", IF(C9=C10,"?","?"))
@BobR
As you have shown you are appending a ? to the number in C10, that doesn't make sense
What are you trying to achieve in your cell
Can you show us numbers in C9/10 and what output you expect?
Hui,
I am basically trying to show either an increase or decrease in value of stocks. In D2 I would have a figure of $900.00 and in E2 a value of -$96.00 (loss on stock) I want it to show the symbols in the formula, but also to show the $ and the 2 decimal points. The way it is now it shows the symbol, no $ sign and the output is 9600. I already have the conditional fomats built in to show either red for decrease in value or green for increase in value and blue for no change. I tried the conditional formats for three arrows, however it will not let me pick = nor let me set an or = for the last arrow (green). I would prefer to make it a conditional format if its possible, but can easily just add it to the existing formula =SUM(F2*G2)-(C2*F2). the formula I posted above is directly out of Chandoos example spreadsheet that you download, I just did not change the values when I posted it. Thank you for the quick response.
ok that for sime reason did not put in the when I am talking about the three arrow conditional format built into excel
ok third time is a charm It should have the greater than and less than symmbols, but they will not post
Hui,
I am sorry I forgot to explain the "?"'s those are the symbols I want to insert. I put ? in the formula to just denote it can be any symbol that is desired
@BobR
If you are using Excel 2007/2010 you can simply do this as follows:
In Cell E2 Format as $ and 2 Decimals
Select Conditional Formatting from the Main Ribbon
Clear all existing Conditional Formats in that cell
.
Select Conditional Formatting from the Main Ribbon again
Icon Sets
Select a set with Up/Down arrows or whatever is appropriate
.
The default sets should work
You may need to adjust the values which trigger up/down arrows
Select E2
Conditional Formatting
Manage Rules
Select the icon set, Edit Rule
I would use the following
Green Up Arrow, >= Value 0.01 Type=Number
Orange Flat Arrow, <0.01 and Value >= 0.0 Type=Number
Red Down Arrow, Value < 0.00 Type=Number
Hui
TYVM. It worked perfectly. Much easier then that formula I was trying to make work. Key was removing all the other conditionals that no longer were neccesary in the fields. Have a great and safe New Year.
[...] Learn Excel Conditional Formatting 85,307 [...]
Hi Hui,
Need help with Conditional formatting. Did not hear back from you.
Column D has a drop down list, column H & J have start and end date. Need formula to conditionaly format cells and change colour for row m2:x2, such that if D2= “Completed”, then considering the date range in H2 & J2 it should colour format specific months for row m2:x2.
I am currenly trying to use – =IF(D$2=”Completed”,AND(M$1>=$H2,M$1<=$J2))
D2 = Drop down menu
M1 = 1/Sep/2011
H1 = 1/sep/2011
J2 = 10/Oct/2011
Where and how do I post the spreadsheet?
Rana
As Hui is away on holiday, let me help you.
Can you see http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/ for similar technique?
If you still have doubts, let me know and I can make an example file with above description and email you.
@Rana
I am on holidays until about the 20th and have limited internet access and no Excel access
Answering posts on an iPhone is not the best and I cannot test answers, which I prefer to do
Ok - I shall wait for you to come back - enjoy your holiday 🙂
Dear Chandoo,
Please help me to compare two columns with no.s and highlight the largest among them.
A B
23 15
45 55
16 89
78 22.
in this i want 23,55,89 and 78 to be highlighted.... is that possible using conditional formating? or can u please explain how to highlight the biggest no in each row, when comparing 2 columns
[...] http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ [...]
Hi Chandoo
Thanks for the great tips.
I was wondering if you know of a way to change the size of the Manage Conditional Formatting Rules Manager Dialogue box in Excel 2010?
It would be nice to see more information.
Alternatively do you know if it is possible to get a list of conditional formatting rules into a worksheet via VBA?
Thanks very much.
Jim Palmer
London, Ontario Canada
[...] easy to understand and your models very lovely to flaunt. Learn here.Starter level Helper level Demigod levelTrace dependents and precedentsIt is not easy to explain your models to others, nor is it easy to [...]
I am using Excel 2007 and have a unique problem. I have one column where the cell contain a name in the first line and a date in the second line, example below. What I would like to do is format that cell with red if the cell contains a date and that date is before todays date. The date always appears below the name but sometime a date is not required. I would like to keep the data in one cell. Anyone have a solution?
"Jack Johnson
3/25/2012"
"Oscar Grouch
N/A"
"Joe Some
3/1/2012"
[...] to test our condition by writing the sumif function as =sumif(condition range, 1, sum range) [learn how you can highlight alternative rows / columns in an excel table] This entry was posted in Excel tricks. Bookmark the permalink. Apple Wins Patent on 3D [...]
Could you link me to your everyday tips and tricks. I find the articles here very interesting and informative.
Thank you very muck
Hi Linda....
Thanks. Please click on below link and enter your email address to join our newsletter.
http://feedburner.google.com/fb/a/mailverify?uri=PointyHairedDilbert&loc=en_US
I conditionally formatted a lot of cells in a spreadsheet using a equals some character type CF. I need to change some of those values but I want to keep the format that was applied by the CF. Is there a way to copy and paste the applied format only so I can delete the CF and keep the fill colors? Kind of like copying a cell wtih a formula and pasting values only except for formats.
Hi Chandoo,
Hope you enjoyed your trip to Australia!!
I have a question which I can't seem to find the answer for on this thread - the conditional formatting where you included an up/down arrow and a circle for no movement.
I wanted to update the formula to take in other symbols - a triangle to the right if no change for instance. But when I insert try to insert another symbol the insert/symbol section is blanked out and I can't select it. I therefore tried to insert using Alt 117 (wingdings3) but it just inserts a 'u' as the actual cell is not wingdings. I'm assuming this is a simple fix, but can't seem to find it at the moment therefore appreciate any assistance - from other users too to put me out of my misery! 🙂
Thanks!
@Jaron
If you want to use the Wing Ding characters, write the Character code down and then setup your formula normally
eg: =If(A1>10,"u","p") or whatever character you need
Once correct, change the font of the cell to WingDings3
Hi Hiu,
Thanks for the reply. I've tried as you suggested, but because the cell has numbers and the symbol - if I change the cell to WingDings3, the numbers no longer show as numbers?
In the example and template Chandoo provided in this article, the cell has both numbers and arrow symbol. The acutal formula seems to have the symbol embeded irrespective of the font?
Any ideas?
Thanks in advance.
Jaron
Love this page..
However, It was something wrong in the section 5. If the values are equal, it does not show the the "number and equal symbol". Could you check again?
Thank you.
I use CF on our golf scorecards for touraments. Players that start on intermediate
holes will be reminded to start marking scores in the light yellow shaded area.
ie. Click on starting hole 7 and the score box for the7th hole will automatically
shade light yellow and so on.
[...] can use either conditional formatting or table formats to quickly add zebra lines to our [...]
Dear Chandoo
A very good morning to you. I would just like to bring to your notice that I am just trying out some of the tricks from the last few days.. I was confused by seeing your formulas. What i was looking for to clear my confusion is in the above tables/cells examples giving for us, please also include row nos. and column a,b,c, etc so that we knows where the data is exactly taken and we can also do the same and can apply the formula accordingly..Otherwise we finding it difficult where to start and also inform clearly in which cell the formula is to be started. Hope you got my point. Thank you and I must admit your hard work for putting such a great efforts for this website, I never seen such a good website like yours for Excel. I am informing this site to my friends. Keep doing good wrk. God bless you.
@Krishna... Welcome to chandoo.org & Thank you for your comments. We usually include some information about row / column numbers in the article so that readers can learn best. Also, please note that the explanations match with downloadable example files. Please open the download file and read the article while practicing the tips. This way, you can learn better.
@ Chandoo:
Thanks a lot for such a quick response. And yes, I downloaded the file now as you said and all my doubts got cleared. Thanks a lot once again for sharing your knowledge to the society as a whole. I feel now even if i join institutes like NIIT, I am sure one cannot learn this much in that institute even after paying such a big fee. Really I feel lucky to come across this site. Have a great day.
Dear Chandoo,
I've been following your posts on conditional formatting and Dashboards, which have been great inspiration for me to produce my own dashboards for a project I'm working on. I've tried to implement VBA code to create 4 conditional formats and have come to the conclusion that it's beyond my capability. I'm wondering if you can help?
I'm creating 23 in-cell bars as charts on the same spreadsheet. The bar charts are produced using this formula for each separate bar =REPT("|",F25") where F25 is replaced with the actual cell reference for each bar chart F26, F27 and so on. Each bar is in an adjacent cell J25 from the value given in in cells F25, F26, and are straight numbers (not calculated value).
I want to be able to change the colour of each bar based on the value that the bar is calculated by. For example, where the cell range is <31 "red", >30<61 "orange", >60<85 "light green, >84 "green".
I want to be able to do this for these cell ranges:
J25:J31; J39:J46 and V39:V46.
Any thoughts.
Best wishes,
Steve
hi,
i got your spreadsheet of CUSTOMER SEARCH CF & would like to know how this has been made.
i'll be grateful if you kindly send me the know how.
best regards.
munna
I am new to conditional formatting and fiormulas - in example 2. (Creating A Quick Project Plan/Gantt Chart Using COnditional Formatting), I am having a difficult time using atcual dates intead of numbering the days 1,2,3, etc. Example: I have a spreadhseet that uses the WEEK 10/01, 10/07, 10/15 (and runs from 1/1/13 through 12/31/15. How would I amend the formula to work with dates instead of the number 1?
Your tips have taught me a great dael - thank you!!
I am new to conditional formatting and formulas – in example 2. (Creating A Quick Project Plan/Gantt Chart Using Conditional Formatting), I am having a difficult time using actual dates instead of numbering the days 1,2,3, etc. Example: I have a spreadsheet that uses the WEEK 10/01, 10/07, 10/15 (and runs from 1/1/13 through 12/31/15. So in row 1 - the dates are indicated by 10/1 instead of 1, 2, 3 etc.
How would I amend the formula to work with week dates instead of the number 1?
Your tips have taught me a great deal – thank you!!
Assuming your start dates are in column A, end dates in column B (A2:A10, B2:B10 respectively) and series of dates in row number 1 from C1:AF1 (just 30 dates),
You can set up conditional formatting in the range C2:AF10 like this:
That should work.
Thank you so much - it works beautifully! I guess I was overthinking the solution as I thought we'd need to reference the date format and all types of references - again, thanks so much for your help!
Chandoo,
I love your page, man. It's always full of great info. I have a question that I hope you can help out with. I've searched around and have not been able to find a solution.
I've used example #5 above to format population figures over time, with up and down triangles and conditional formatting to make "down" red and "up" blue. Many of my numbers are in the thousands, and I can't figure out how to display commas to separate the thousands. Since the cells contain up/down symbols, Excel treats the cell as text, and I can't get the thousands separators in there (e.g. cell reads '1500 ^' when I want '1,500 ^').
Do you know of any way to show the thousands separators in these cells? Any help would be much appreciated. Thank you, sir, and keep up the good work!
Best wishes,
Kevin
@Kevin... thanks for your comments and welcome to Chandoo.org.
In your case, the original formula would have read something like:
=ROUND(C10,0) & " " & IF(C9...You can use this instead:
=TEXT(ROUND(c10,0),"#,##") & " " & LOGIC FOR SYMBOL
Chandoo, that worked like a charm. Thanks, man!
Hi Chandoo,
Thanks a lot for the useful tips. I'm not able to get the symbols (the triangles) for the dashboard in excel 2010. Can anybody please help?
Thanks a lot.
Hi Chandoo,
I m not able to get the Symbol in the formulas can u pls show me the tricks how to use it.
Aw, this was a really nice post. Taking the time and
actual effort to create a great article… but what can I say… I
hesitate a whole lot and don't seem to get nearly anything done.
Hi I have built a task progress calendar. But I was unable to highlight two row for the same task. For example, I have a task in row and I have start weeks and duration very similar to how you have in your example. But how do I highlight the percentage completion if the tasks occur in multiple times across the columns?
@Ranga
Can you post a sample file so we can see what your dealing with?
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
[…] better than Arial. May be you like Georgia compared to Arial. But when you set out to create that gantt chart for your new project you have to manually change the font from Arial to Georgia everytime. No […]
Hi
i am having some problems. after i choose font windings , the number turns to symbol.
can you solve this matter.
i see this tip is useful for my job.
many thanks
@Phuong
That what its meant to do
But the formula will still work
Can you post the formula and the Font you are using ?
Hi
please follow this link
http://www.mediafire.com/?hx8788a6c9685d1
in "TONG HOP" sheet. i would like to create dash board.
i use conditional formatting , but i recognize that it is not better than this tip.
thank for supporting me
@Phuong
I assume you are referring to cell AC7
You cannot mix two fonts in a Formula in one cell
You could add another column AD
Then in AC7 in Times New Roman Font =ROUND(AK7*100,0)
and format AC7 as %
and in AD 7 in Wingding Font =IF(AK7
thanks Hui, but it's difficult for me. is there any solution?
Hi
How can I use conditional formatting for a part or a word of a text in one cell or in a range.
ManyThanks
Hi, i want to highlight the lowest 3 prices quoted by vendors. All have been tabulated in rows and columns. These prices are by location and by vendor
[…] There's a Gantt Chart tutorial here: Conditional Formatting in Excel | Chandoo.org - Learn Microsoft Excel Online […]
Thanks designed for sharing such a fastidious opinion,
article is pleasant, thats why i have read it entirely
[…] learned: using features like formulas & conditional formatting to make gantt […]
Your style is very unique in comparison to other people I have read
stuff from. Thanks for posting when you have the opportunity,
Guess I will just bookmark this site.
Dear Friend,
I needed you help using Conditional Formatting.
I have an excel sheet, but I wasn't able to change the color of the cell when Yes is typed.
Please help....very urgent
I work with Excel 2010. Is there any way to format/program cells in an Excel template so that when the data merges into those cells (from another program I work with), all number and word combos containing hyphens will stay together and not wrap down (separate to) the next line AT the hyphen if they get positioned at the end of a line?
I every time spent my half an hour to read this website's content every day along with a
mug of coffee.
I think this is one of the most important info for me. And
i'm glad reading your article. But wanna remark on few general
things, The website style is great, the articles is really excellent : D.
Good job, cheers
My partner aand I absolutely love your blog and find nearly
all of your post's to bbe precisely what I'm looking for.
can you offer guest writers to write content to suit your needs?
I wouldn't mind writing a post orr elaborating on a number of the subjects
you write about here. Again, awesome website!
Particularly informative looking forth to coming back.
Thankfulness to my father who told me regarding this web site, this weblog is genuinely awesome.
Hi,
I ma preparing a score sheet and the out come finally will be three values (most, moderate and least). the final score will come through summing up of few columns, i.e. sum (A5:F5). i want to categorise the final score into three category and those should be with colour code and text in a single cell. I have to share this format with large number of stakeholders. How to do this in excel.
@Sumoni
You would typically use a formula like:
=IF(SUM(A5:F5)>10,"High",IF(SUM(A5:F5)>5,"Medium","Low"))
or a VLookup formula to achieve a similar result.
You can then apply Conditional Formatting to color the cells as required.
After I originally left a comment I seem to have clicked on the -Notify me when new comments
are added- checkbox and from now on whenever a comment is added I get 4 emails with the exact same comment.
Perhaps there is an easy method you are able to remove me from that service?
Thanks!
I want to give serial number to the check marks that are outcome of this formula;
=IF(AND(K12>=0,K12<=1),"a","") i.e., the above formula is used to get check marks in cells R12. for all the enteries with code 1 in K12 leads to a check mark in R12. Now that I want to give running serial number (1,2,3...so on) for these check marks. what is the appropriate formula i need to insert in R12 along with the one noted here.
Could anyone please help me.
Ajit Phape
There is definately a lot to find out about this subject.
I really like all of the points you've made.
Hiiii Chandoo....
I need a help in excel. I want to color some row which are visible where as there are many rows which are hidden. While i am applying the color in the visible row in that time the rows which are hidden also got colored. Can you please help me out or advise me how to color the visible row only (without coloring the hidden rows)
Hi Rashmi,
Welcome to Chandoo.org and thanks for your comments.
Follow below steps to color only visible cells.
1. Select the entire range (both visible & hidden rows)
2. Press F5 and click on Special.
3. Chose "Visible cells only" as the option.
4. Click ok.
5. Now all the visible cells are selected. Fill color
6. Done.
Thanks a lot for the prompt reply, i have another question also suppose there are many rows/column left blank in my work sheet, i want to hide all the blank cell in worksheet with a single click. Can u please guide me what is the procedure.
You are welcome.
From the Go to Special dialog, you can select all blank cells. Once selected, hide the rows by pressing CTRL+9 (or going to Home > Format > Hide & Unhide > Hide Rows)
in excell 2010 how to do the following,
A B C D
X310 Region (2011) (2012) Increase % Increase
R01 3,605 3,853 248 6.88%
R02 3,966 3,842 -124 -3.13%
R03 3,760 4,035 275 7.31%
R04 3,777 4,063 286 7.57%
R05 3,974 3,725 -249 -6.27%
R06 3,656 3,937 281 7.69%
R07 3,664 3,875 211 5.76%
R08 3,715 3,844 129 3.47%
Total 30,117 31,174 1,057 29.29%
In the range E7:E14, apply a conditional format that adds a Top/Bottom Rule to
display the highest number in the range in dark green text with a green fill
Regards.
columns E and F disappeared from view.
[…] Courtesy: chandoo.org […]
Hi all
Item 3, could you please help how to insert this | into the formula =REPT("|",C8/5)
Much appreciated
Hi,
Press Alt gr and the button next to the letter "z".
Chandoo:
Thanks for these tips. I have applied conditional formatting to assign fill color in some columns and am using "form" to enter data in these columns. Is there any way that the fill color of the cells can also be made to appear in the form?
Thanks
Bharath
[…] use a nifty trick I picked up from chandoo.org a few years back, and “ported” it so to say to mac for numbers. Of course in comparison to […]
what condition to put in countif() i am not so well versed..suggest plz 🙂
@Nisha
Have a read of: http://chandoo.org/wp/2008/11/12/using-countif-sumif-excel-help/
Hi Chandoo
I am a big fan of yours and an ardent follower of what you do in excell.
Pleasse help me with formula used in point 5 of conditional formatting article. I am using excell 2013 and i am unable to use up and down symbols in formula. So i am not able to format the table the way you did.
Please help
Hi Chandoo,
This was very helpful, not often one can find tips on conditional formatting. Keep up the good work.
Iam getting values to sheet2 from sheet1 in excel 2013...Formula is like
='sheet1'!A8.... In sheet1 that perticular cell is in bold. but in sheet2 iam getting normal text instead of bold....
solve my problem
How to convert pdf to excel sheet?
To not mention the ones which don't have a shot at all.
Hi Chandoo,
Thank for these excellent tips. I need a conditional formatting formula in the range A1:H8 where there are only 2 input values, 0 and 1, and;
- all cells with 0 in them has the background color of white,
- all cells with 1 in them has the background color of black.
- In the beginning, all cells have the input value of 0 in them and hence has the background color of white.
Requirement:
- Whenever I change the input value in a cell, all cells in the same row and column including the cell itself, change their color.
For instance, let's say cell D4 has the input value of 0 and hence has the background color of white. If I enter 0 in cell D4, I want cells D1:D8 and A4:H4 to change their color. Looking forward to your reply.
@Radiostar... Your requirements seem to contradict each other.
For example, let's say D4 is blank and D5 has 1. By definition, D4 should have white and D5 should have black colors.
Then you enter a value in D4 (say 0). Then should D5 be black or White?
Dear Chandoo,
I think I couldn't make myself clear. There are no blank cells in the range A1:H8. They are all filled with 0 in the beninning. Actually, what the numbers are is of no importance . What matters is whenever I change the value in a cell in the range A1:H8 where there are only two possible input values, the cell itself and all the cells in the same row and column change color.
Let the input values be 1 (hence all cells with 1 are white) and 2 (hence all cells with 2 are black) and initially, all the cells have 1 in them (meaning all cells in the range are in white in the beginning.
So if you change D4 to 2, the color of the cells in D1:D8 and A4:H4 change to black.
If you then change B7 to 2, the color of the cells in B1:B3, B5:B8, A7:C7 and E7:H7 change to black; and the color of the cells in B4 and D7 change back to white etc.
I know that it is a lot to ask but it's almost impossible for me to come up with such a formula given little Excel knowledge I have.
I don't understand #5 Dashboard. It appears you have mixed the Excel formula with the intended purpose of the formula.
=ROUND(C10,0) & " " & IF(C9 Essentially, what we are doing is, whenever the cell value is more than its predecessor in the data table we are appending the symbol â–² (go to menu > insert > symbols and look for the above one) etc.
Can you please share the formula syntax exactly as it should be?
First add counting from 1 to 10 till last row in column B Every time it should create New Workbook Then create 1 to 10 tabs in that book copy the respected data on respected tabs
urgent me
plz send is this email id
Hi Chandoo and followers.
I am trying to highlight cells where "a" values repeat 3 times.
My conditional formula reads:
=COUNTIF(A3:C3,"=a")>2
and I want all three cells with an "a" to highlight. (and then to copy that formatting across a row range with the address values increasing appropriately)
BUT, Excel is only highlighting the FIRST cell of any three. not all three "a" value cells.
Any Advice please?
@Mike... Welcome to Chandoo.org and thanks for posting your question.
Use the below rule instead.
=COUNTIF($A3:$C3,"=a")>2
Chandoo,
Thank you for that but it does not do what I need it to. Unless I have misunderstood.
I need to copy the formula across a row so that any 3 "a" in sequence will activate the condition, not the A,B,C cells specifically.
I could write a new formula for each set of three but for 31 columns, 46 rows it is too much. So I don't wish to use absolutes.
Any other ideas?
Thank you
@Mike
I'd suggest asking the question in the Chandoo.org Forums http://forum.chandoo.org/
Attach a sample file with an example of what you are after
How do you inset these symbols in the formula? "?", IF(E9=E10,"?","?
I favour the shades!
I'm trying to make "grantt chart excel" , download of "grantt chart template" from you.
I have 3 date data;
1. The date of order entry
2. terdeliver order date (overdue, ongoing, already completed)
3. date view today
I want in the column "done" (%), are:
1. describe on time delivery rate (the duration of the plan duration of the plan)
3. as well as the conditions of view as of today
What formula can I use to count to 3 points above
thank you
I want to copy the formula down the column, but only on every 3rd row. I want it to be copied the data in A1 A2 A3 A4 into B1, B4, B7, B10, and so on... Does anyone know how to do this?
How to Highlight Top three Name of the customer
Please approve me
Thanks Chandoo for sharing this valuable article. these are very useful tricks for conditional formatting.
Hi- In Excel, I need to conditionally format a column of text whenever the text in that cell exceeds a limit of 8 characters (and that includes spaces). Can anyone help me do that?
Greg,
Highlight the cell, select Conditional Formatting form the home tab, select new rule.
Select "use a formula to determine which cells to format"
The formula you want is =LEN(J1)>8
Where J is the column address, change as required.
choose your format option and save.
Copy the cell and paste special the format condition to remaining cells.
All done...
Mike.
Thank you Mike! I’ll give it a try. I appreciate it!
It was fun reading this article.
Just the other day I was coloring the alternate rows of a table in Excel one by one. It was really monotonous. Then I learned about Conditional Formatting from the article and it was pretty easy to it.
Hi, I try to create a excel sheet to keep track the job flow. But seem like it too hard. Can you help me ?
Great Work!